package com.asia.execl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ImportExcell {
    private static final Log log = LogFactory.getLog(ImportExcell.class);

    public List<Map<String, String>> readExcel(File file) {
        log.info("��ȡexcel��ʼ...");
        List<Map<String, String>> dataset = new ArrayList<Map<String, String>>();

        try {
            // �������File����ΪFileInputStream;
            FileInputStream inputStream = new FileInputStream(file);
            // �õ�������
            HSSFWorkbook book = new HSSFWorkbook(inputStream);
            // �õ���һҳ
            HSSFSheet sheet = book.getSheetAt(0);
            // �õ���һ���������
            Iterator<Row> rowIterator = sheet.rowIterator();

            // �õ���һ�У�Ҳ���Ǳ�����
            @SuppressWarnings("unused")
            Row titleRow = rowIterator.next();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Map<String, String> map = this.creatObjectByRow(row);
                dataset.add(map);
            }
        } catch (FileNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (IOException e2) {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        } catch (Exception e) {
            // TODO: handle exception
        }
        log.info("��ȡexcel����...");
        return dataset;
    }

    private Map<String, String> creatObjectByRow(Row row) {
        // �е�������
        Iterator<Cell> cellBody = row.cellIterator();
        // ����һ�е���
        int col = 1;
        Map<String, String> map = new HashMap<String, String>();
        while (cellBody.hasNext()) {
            String field = String.valueOf(col++);
            Cell cell = cellBody.next();
            if (cell != null) {
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:     // �ַ�
                        map.put(field, StringUtils.trim(cell.getStringCellValue()));
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:    // ����
                        map.put(field, StringUtils.trim(cell.getStringCellValue()));
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:    // ����
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {// �Ƿ�Ϊ���ڸ�ʽ
                            map.put(field, String.valueOf(cell.getDateCellValue()));
                        } else {
                            Double cellValue_dob = cell.getNumericCellValue();// ��ȡcell�����
                            if (String.valueOf(cellValue_dob).length() > 11) { // ����ȡ�������ֻ����,��Ҫƥ�����ָ�ʽ
                                DecimalFormat format = (DecimalFormat) NumberFormat.getInstance();
                                //format.applyPattern("00000000000");
                                map.put(field, format.format(cellValue_dob));
                            } else { // ����ȡ�����ǱȽ϶̵����֣���ȥ��β��.0������ʾ
                                map.put(field, cellValue_dob.toString().substring(0, cellValue_dob.toString().length() - 2));
                            }
                        }
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:    // ��ʽ
                        map.put(field, String.valueOf(cell.getNumericCellValue()));
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:      // ��
                        map.put(field, StringUtils.trim(cell.getStringCellValue()));
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:      // �쳣
                        map.put(field, StringUtils.trim(cell.getStringCellValue()));
                        break;
                    default:
                        map.put(field, StringUtils.trim(cell.getStringCellValue()));
                        break;
                }
            }
        }
        return map;
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        ImportExcell inport = new ImportExcell();
        File file = new File("D:\\testOne.xls");
        List<Map<String, String>> mapList = inport.readExcel(file);
        for (Map<String, String> map : mapList) {
//          Iterator<Entry<String, String>> iterator = map.entrySet().iterator();
//          while (iterator.hasNext()) {
//              Map.Entry<String, String> entry = iterator.next();
//              String key = entry.getKey();
//              String value = entry.getValue();
//              System.out.println("key:" + key + "\tvalue:" + value);
//          }
            TestVo t = new TestVo();
            t.setName(map.get("1"));
            System.out.println(t.getName());
        }
    }
}
